#!/bin/bash
# Check all the SQL statements to ensure they are correct
cd /u/cem/cdc/web
#Colours
NC='\033[0m' # No Color
Black='\033[0;30m'
DarkGray='\033[1;30m'
Blue='\033[0;34m'
LightBlue='\033[1;34m'
Green='\033[0;32m'
LightGreen='\033[1;32m'
Cyan='\033[0;36m'
LightCyan='\033[1;36m'
Red='\033[0;31m'
LightRed='\033[1;31m'
Purple='\033[0;35m'
LightPurple='\033[1;35m'
BrownOrange='\033[0;33m'
Yellow='\033[1;33m'
LightGray='\033[0;37m'
White='\033[1;37m'

sqlGrepFile="/u/tmp/transGrep.txt"

#Default values
searchType="-f" #Default Search type
returnType="-a" #Default Output type
showSearchedFiles=0
fileParam=""
scriptName=`basename $0`
sqlModSearch="DELETE\sFROM\s\|UPDATE\s\|INSERT\sINTO\s"
clear && printf '\033[3J' #Clear the screen

function execute
{
  echo "Processing........"
  if [[ $returnType == "-e" ]]; then #Search for odb_sql_exec()
    grep_files $returnType
    check_exec "$sqlGrepFile"
  elif [[ $returnType == "-m" ]]; then #Search for odb_sql_mod()
    grep_files $returnType
    check_mod "$sqlGrepFile"
  elif [[ $returnType == "-a" ]]; then #Search for both
    grep_files "-m"
    check_mod "$sqlGrepFile"
    grep_files "-e"
    check_exec "$sqlGrepFile"
  else
    echo -e "${Red}Error${NC}";
    exit
  fi
}

function grep_files
{
  filesToGrep=""
  if [[ $1 == "-e" ]]; then
    patt="odb_sql_exec\|odb_free"
  elif [[ $1 == "-m" ]]; then
    patt="$sqlModSearch\|odb_sql_mod"
  else
    patt="odb_sql_\|odb_free"
  fi

  if [[ "$searchType" == "-f" ]]; then #Search via Filenames
    if [[ "$fileParam" == "" ]]; then
      #No filenames passed in, use list of files in SVN status
      echo -e "${Purple}-------------------------------------------${NC}";
      echo -e "Searching all outstanding files${NC}";
      if [[ $showSearchedFiles -eq 1 ]]; then
        echo -e "Files being searched${NC}";
        echo -e "";
      fi
      while read op fname dummy
      do
        if [[ $showSearchedFiles -eq 1 ]]; then
          echo "$fname"
        fi
        fileParam="$fileParam $fname"
      done < <(svn status)
      echo -e "${Purple}-------------------------------------------${NC}";
    fi

    #Loop through all the files
    for file in $fileParam
    do
      if [ ! -f $file ]; then
        #File does not exist, find it
        lastChar="${file: -1}"
        if [[ "$lastChar" == "/" ]]; then
          #Folder passed in
          filePath="$file"
        else
          filename=`basename $file`
          filePath=$(find -name "$filename") #Find filepath to file
          filePath=${filePath:2} #Remove ./ from file path
        fi
      else
        filePath="$file"
        filename=`basename $file`
      fi
      filesToGrep="$filesToGrep $filePath" #Append file paths to includes
    done
    grep -rn "$patt" --exclude="*.swp" --exclude="*.old" $filesToGrep > \
      "$sqlGrepFile"
  else
    #Search via file prefixes
    fileParam="$(echo -e "${fileParam}" | tr -d '[[:space:]]')" #Remove spaces
    if [[ $showSearchedFiles -eq 1 ]]; then
      #List files that are to be searched
      showSearchedFiles=0
      echo -e "${Purple}-------------------------------------------${NC}";
      echo -e "Files being searched${NC}";
      grep -rnl --include="*$fileParam*" --exclude="*.swp" --exclude="*.old" \
            "\$Id" *
      echo -e "${Purple}-------------------------------------------${NC}";
    fi
    grep -rni --include="*$fileParam*" --exclude="*.swp" --exclude="*.old" \
      "$patt" * > "$sqlGrepFile"
  fi
}

function check_mod
{
  echo -e "";
  echo -e "-----------------------------------${NC}";
  echo -e " Checking odb_sql_mod() statements${NC}";
  echo -e "-----------------------------------${NC}";

  prevLine=""
  sqlModRequired=0
  modTotal=0
  sqlTotal=0
  errorFound=0
  #Read the SQL grep file
  while read match
  do
    IFS=':' read -ra split <<< "$match" #Split string on ':'
    lne=$(echo "${split[2]}" | sed -e 's/^[ \t]*//') #Remove leading spaces
    if [[ "$lne" == //* ]]; then #Line begins with //, its a comment, Skip it
      continue
    fi

    #Determine in the line is an SQL statement or odb_sql_mod()
    sqlCount=$(grep -oi "$sqlModSearch" <<< "$match" | wc -l)
    modCount=$(grep -o "odb_sql_mod" <<< "$match" | wc -l)

    if [[ $sqlModRequired -eq 1 ]] && [[ $modCount -eq 0 ]]; then
      #Didn't find an odb_sql_mod() statement after the SQL statement,
      #Output line
      errorFound=1
      split_grep "$prevLine"
    fi
    prevLine=""
    sqlModRequired=0

    if [[ $sqlCount -gt 0 ]]; then #Found an SQL statement
      sqlTotal=$(($sqlTotal + 1));
      prevLine="$match"
      sqlModRequired=1
    elif [[ $modCount -gt 0 ]]; then #Found an odb_sql_mod() statement
      modTotal=$(($modTotal + 1));
    else
      echo -e "${Red}***  Other  *** $match ${NC}";
    fi
  done < "$sqlGrepFile"

  if [[ $sqlModRequired -eq 1 ]] && [[ $modCount -eq 0 ]]; then
    split_grep "$prevLine"
  fi

  #Check if there were any errors founD
  if [[ $errorFound -eq 0 ]]; then
    echo -e "${Green} Files are OK!!${NC}";
  else
    echo -e "${Red}------------------------------------------------------${NC}";
    echo -e "${Red} The lines above should be modified to use odb_sql_mod${NC}";
    echo -e "${Red} Instead of odb_sql_exec()${NC}";
    echo -e "${Red}------------------------------------------------------${NC}";
  fi

  #Output total values
  echo -e "";
  echo -e "${Blue} $sqlTotal : INSERT/DELETE/UPDATE SQL statements  ${NC}";
  echo -e "${BrownOrange} $modTotal : odb_sql_mod  ${NC}";
  echo -e "${NC} ^ Both count values should match ${NC}";
}

function check_exec
{
  echo -e "";
  echo -e "-----------------------------------${NC}";
  echo -e " Checking odb_sql_exec() statemnet ${NC}";
  echo -e "-----------------------------------${NC}";
  prevLine=""
  odbFreeRequired=0
  execTotal=0
  freeTotal=0
  declare -a unmatchedLines
  #Read the SQL grep file
  while read match
  do
    execCount=$(grep -o "odb_sql_exec" <<< "$match" | wc -l) #Count this line
    freeCount=$(grep -o "odb_free" <<< "$match" | wc -l) #Count this linea

    #Expecting an odb_sql_exec(), found odb_free()
    if [[ $odbFreeRequired -eq 0 ]] && [[ $freeCount -eq 1 ]]; then
      IFS=':' read -ra free_lineArr <<< "$match" #Split string on ':'
      freeStringPos=$(expr ${#free_lineArr[@]} - 1) #Get last position in Array
      freeString=${free_lineArr[freeStringPos]}

      lne=$(echo "$freeString" | sed -e 's/^[ \t]*//') #Remove leading spaces
      #Remove 'odb_free(' and trailing characters after ')'
      output="$( cut -d ')' -f 1 <<< "${lne:9}" )";
      pos=$(expr ${#unmatchedLines[@]} - 1) #Get last position in Array
      for (( i=$pos; i>=0; i-- )); #Read through array backwards
      do
        #Check if odb_free is for an unmatched line
        exLine=${unmatchedLines[$i]}
        if [ "$exLine" = "" ]; then
          continue #Skip Blank lines
        fi
        IFS=':' read -ra exec_lineArr <<< "$exLine" #Split string on ':'
        execStringPos=$(expr ${#exec_lineArr[@]} - 1) #Get last position in Arr
        execString=${exec_lineArr[execStringPos]}

        IFS='=' read -ra execStrArr <<< "$execString" #Split string on '='
        lne="$(echo -e "${execStrArr[0]}" | tr -d '[[:space:]]')" #Remove spaces
        if [ "$output" = "$lne" ] ; then
          #odb_free() has a match, remove unmatched line from the array
          unset unmatchedLines[$i]
          break
        fi
      done
    fi

    #Expecting an odb_free() after odb_sql_exec()
    if [[ $odbFreeRequired -eq 1 ]] && [[ $freeCount -eq 0 ]]; then
      #Found odb_sql_exec() instead, add line to unmatchedLines array
      arrPos=${#unmatchedLines[@]}
      unmatchedLines[$arrPos]=${prevLine}
    fi
    prevLine=""
    odbFreeRequired=0

    if [[ $execCount -gt 0 ]]; then
      execTotal=$(($execTotal + 1));
      prevLine="$match"
      odbFreeRequired=1
    elif [[ $freeCount -gt 0 ]]; then
      freeTotal=$(($freeTotal + 1));
    else
      echo -e "${Red} ***  Other  *** ${NC}";
    fi
  done < "$sqlGrepFile"

  if [[ $odbFreeRequired -eq 1 ]] && [[ $freeCount -eq 0 ]]; then
    #Found odb_sql_exec() instead, add line to unmatchedLines array
    arrPos=${#unmatchedLines[@]}
    unmatchedLines[$arrPos]=${prevLine}
  fi

  len=${#unmatchedLines[@]}
  if [[ $len -eq 0 ]]; then
    echo -e "${Green} Files are OK!!${NC}";
  else
    #Loop through unmatched lines
    for (( i=0; i<${len}; i++ ));
    do
      exLine=${unmatchedLines[$i]}
      split_grep "$exLine"
    done
    echo -e "${Red}------------------------------------------------------${NC}";
    echo -e "${Red} The lines above are not using odb_free() after ${NC}";
    echo -e "${Red} odb_sql_exec() is finished${NC}";
    echo -e "${Red}------------------------------------------------------${NC}";
  fi

  echo -e "";
  echo -e "${Blue} $execTotal : odb_sql_exec  ${NC}";
  echo -e "${BrownOrange} $freeTotal : odb_free  ${NC}";
  echo -e "${NC} ^ Both count values should match ${NC}";
  echo -e "";
}

function split_grep
{
  IFS=':' read -ra grepArr <<< "$1" #Split string on ':'
  lneNumPos=$(expr ${#grepArr[@]} - 2) #Get the line number position in Array
  matchStringPos=$(expr ${#grepArr[@]} - 1) #Get match string position in Array
  matchString=${grepArr[matchStringPos]}

  if [[ $lneNumPos -eq 1 ]]; then
    output="${Purple}${grepArr[0]}:${Green}${grepArr[1]}"
  else
    output="${Green}${grepArr[0]}"
  fi

  lne=$(echo "$matchString" | sed -e 's/^[ \t]*//') #Remove leading spaces
  echo -e "$output${NC}: $lne";
}

function helper {
  echo -e "${Purple}------------------------------------------------------${NC}"
  echo -e "  Check all SQL execute statemnets to ensure odb_free() is called"
  echo -e "  v1.7"
  echo -e "${Purple}------------------------------------------------------${NC}"
  echo -e "  $scriptName [Type] [Return] [Files]"
  echo -e "   [Type]  => Type of files to search"
  echo -e "               ${Purple}-f${NC}  Filenames - Search only these "\
                          "files(Default)"
  echo -e "               ${Purple}-p${NC}  File Prefix - Search all file "\
                          "that contain this text"
  echo -e "   [Return]=> Type of information to return"
  echo -e "               ${Purple}-e${NC} => Check exec statements(Default)"
  echo -e "               ${Purple}-m${NC} => Check mod statements"
  echo -e "               ${Purple}-a${NC} => Run both -e and -m together"
  echo -e "   [Files] => List of files, separated by space"
  echo -e "   [opts]  => Optional arguments"
  echo -e "               ${Purple}-d${NC} => List files that were checked"
  echo -e "  Examples"
  echo -e "  $scriptName acc_permiss_aj.php acc_permiss.php acc_permiss.js "
  echo -e "  $scriptName -p acc_ "
  echo -e "  $scriptName -f acc_permiss_aj.php acc_permiss.php acc_permiss.js "
}

#Get Parameters
for arg in "$@"
do
  if [[ "$arg" == *"?"* ]] || [[ "$arg" == *"help"* ]]; then
      helper
      exit
  fi

  case "$arg" in
    -e|-m|-a)
      returnType="$arg"
    ;;
    -d)
      showSearchedFiles=1
    ;;
    -p|-f)
      searchType="$arg"
    ;;
    *)
      fileParam="$fileParam $arg"
    ;;
  esac
done

if [[ "$searchType" == *"?"* ]] || [[ "$searchType" == *"help"* ]]; then
    helper
elif [[ "$fileParam" == "" ]] && [[ "$searchType" == "-p" ]]; then
  echo -e "${Red}Missing parameter !!${NC}"
  helper
elif [[ "$searchType" != "-p" ]] && [[ "$searchType" != "-f" ]]; then
  echo -e "${Red}Invalid Parameter !!${NC}"
  helper
else
  execute $searchType $fileParam $returnType
  rm -f "$sqlGrepFile";
fi

